﻿using CNKI.TPI.Web.Base;
using CNKI.TPI.Web.Report.IDAO;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace CNKI.TPI.Web.Report.DAO
{
    public class UserOperateDAO : BaseDAO, IUserOperateDAO
    {
        public DataTable GetUserOperateStatisticsInfo(int searchType, DateTime? startDate, DateTime? endDate, string startip, string endip)
        {
            DataTable dt = null;
            try
            {
                string sql1 = @"SELECT 
                                    a.username username, SUM(IFNULL(a.search,0)) search, SUM(IFNULL(a.browse,0)) browse, SUM(IFNULL(a.download,0)) download
                                FROM
                                    tpi.accessstatistics a
                                {0}
                                GROUP BY a.username
                                ORDER BY a.username";
                string sql2 = @"SELECT 
                                    a.IP username, SUM(IFNULL(a.search,0)) search, SUM(IFNULL(a.browse,0)) browse, SUM(IFNULL(a.download,0)) download
                                FROM
                                    tpi.ipaccessstatistics a
                                {0}
                                GROUP BY a.IP
                                ORDER BY a.IP";
                string where = "";
                if (startDate != null && endDate != null)
                {
                    where = "where a.date >= '" + ((DateTime)startDate).ToString("yyyy-MM-dd") + " 00:00:00' and a.date <='" + ((DateTime)endDate).ToString("yyyy-MM-dd") + " 23:59:59'";
                }
                else if (startDate != null)
                {
                    where = "where a.date >= '" + ((DateTime)startDate).ToString("yyyy-MM-dd") + " 00:00:00'";
                }
                else if (endDate != null)
                {
                    where = "where a.date <= '" + ((DateTime)endDate).ToString("yyyy-MM-dd") + " 23:59:59'";
                }

                if (searchType == 0)
                {
                    dt = context.ExecuteSql(string.Format(sql1, where), "mysql");
                }
                else
                {
                    string ipstr = "";
                    if (!string.IsNullOrEmpty(startip) && !string.IsNullOrEmpty(endip))
                    {
                        ipstr = " inet_aton(a.IP) between inet_aton('"+startip+"') and inet_aton('"+endip+"')";
                    }
                    else if (!string.IsNullOrEmpty(startip))
                    {
                        ipstr = " and inet_aton(a.IP) >= inet_aton('" + startip + " ')";
                    }
                    else if (!string.IsNullOrEmpty(endip))
                    {
                        ipstr = " and inet_aton(a.IP) <= inet_aton('" + endip + " ')";
                    }
                    if (where != "")
                    {
                        where = where + " and " + ipstr;
                    }
                    else
                    {
                        where = where + " where " + ipstr;
                    }
                    dt = context.ExecuteSql(string.Format(sql2, where), "mysql");
                }
            }
            catch (Exception ex)
            {
                LogOpr.Error(ex.ToString());
            }
            return dt;
        }
        public DataTable GetSubmitInfo(string username, DateTime? startDate, DateTime? endDate)
        {
            DataTable dt = null;
            try
            {
                string sql1 = @"SELECT 
                                   s.UserName,d.dbname ,sum(ifnull(s.Submit,0)) as SubmitCount,sum(ifnull(s.Modify,0)) as ModifyCount,
                                   sum(ifnull(s.Approved,0))as ApprovedCount,sum(ifnull(s.dismissed,0)) as DismissedCount
                                FROM
                                    tpi.submitonlinestatistics s
                                inner join tpi.dbinfo d on d.dbid = s.dbid
                                {0}
                               group by s.username,s.dbcode order by s.username";
                string where = "";
                if (startDate != null && endDate != null)
                {
                    where = "where s.date >= '" + ((DateTime)startDate).ToString("yyyy-MM-dd") + " 00:00:00' and s.date <='" + ((DateTime)endDate).ToString("yyyy-MM-dd") + " 23:59:59'";
                }
                else if (startDate != null)
                {
                    where = "where s.date >= '" + ((DateTime)startDate).ToString("yyyy-MM-dd") + " 00:00:00'";
                }
                else if (endDate != null)
                {
                    where = "where s.date <= '" + ((DateTime)endDate).ToString("yyyy-MM-dd") + " 23:59:59'";
                }
                if (!String.IsNullOrEmpty(username))
                {
                    if (where != "")
                    {
                        where += " and s.UserName LIKE '%" + username + "%'";
                    }
                    else {

                        where = " where s.UserName LIKE '%" + username + "%'";
                    }
                }

                dt = context.ExecuteSql(string.Format(sql1, where), "mysql");

            }
            catch (Exception ex)
            {
                LogOpr.Error(ex.ToString());
            }
            return dt;
        }

        public DataTable GetUserWorkInfo(string username, string workType, DateTime? startDate, DateTime? endDate)
        {
            DataTable result = null;
            try
            {
                string sql = @"SELECT logusername, workType, count(*) as rowCount
                            FROM TCMMLOGVIEW
                            {0}
                            GROUP BY logusername";
                string where = string.Format("WHERE LOGOBJECTNAME='{0}'", workType); ;

                if (startDate != null && endDate != null)
                {
                    where += " and logtime >= '" + ((DateTime)startDate).ToString("yyyy-MM-dd") + " 00:00:00' and logtime <='" + ((DateTime)endDate).ToString("yyyy-MM-dd") + " 23:59:59'";
                }
                else if (startDate != null)
                {
                    where += " and logtime >= '" + ((DateTime)startDate).ToString("yyyy-MM-dd") + " 00:00:00'";
                }
                else if (endDate != null)
                {
                    where += " and logtime <= '" + ((DateTime)endDate).ToString("yyyy-MM-dd") + " 23:59:59'";
                }
                if (!String.IsNullOrEmpty(username))
                {
                    where += " and logusername = '" + username + "'";
                }
                result = Context.ExecuteSql(string.Format(sql, where));
                if (result != null && result.Rows.Count > 0)
                {
                    foreach (DataRow dr in result.Rows)
                    {
                        dr[1] = workType;
                        dr[0] = StringHelper.RemoveHighlightFlag(dr[0].ToString());
                    }
                }
            }
            catch (Exception ex)
            {
                LogOpr.Error(ex.ToString());
            }
            return result;
        }
    }
}
